package com.bestseller.rtr;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.StringReader;
import java.lang.reflect.InvocationTargetException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;
import org.xml.sax.InputSource;

import com.bestseller.pojo.RtrBean;
import com.bestseller.pojo.Sku;


public class TrtImport {
	public static void main(String[] args) throws Exception {
		File file=new File("c://rtrchuli.xls");
		InputStream is=null;
		Workbook wb=null;
		try {
			 is=new FileInputStream(file);
			 wb=WorkbookFactory.create(is);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		Sheet sheet=null;
		sheet=wb.getSheet("sheet1");
		List<RtrBean> list=new ArrayList<RtrBean>();
		System.out.println(sheet.getLastRowNum()+1);
		for(int i=0;i<=sheet.getLastRowNum();i++){
			Row row=sheet.getRow(i);
			Cell cell=row.getCell(0);
			String result=getCellValue(cell);
			int start=result.indexOf("<SOAP-ENV:Body");
			int end=result.indexOf("</soap-env:Envelope>");
			String newResult=result.substring(start,end);
			Map<String,Object> map = xmlElements(newResult);
			
			RtrBean rtr=new RtrBean();
			try {
				
				List<Sku> skus=(List<Sku>) map.get("skus");
				RtrBean map2Bean = (RtrBean) convertMap(rtr.getClass(),map);
				if(skus.size()!=0){
					for(int j=0;j<skus.size();j++){
						if(j>0){
							map2Bean = (RtrBean) convertMap(rtr.getClass(),map);
							System.out.println(map2Bean.getUniqueKey());
							map2Bean.setUniqueKey(map2Bean.getUniqueKey()+" "+j);
						}
						map2Bean.setSkuId(skus.get(j).getSkuId());
						map2Bean.setQuantity(skus.get(j).getQuantity());
						map2Bean.setHostState(skus.get(j).getHostState());
						map2Bean.setHeld(skus.get(j).getHeld());
						map2Bean.setReasonCode(skus.get(j).getReasonCode());
						list.add(map2Bean);
					}
				}else{
					list.add(map2Bean);
				}

			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		Workbook wb2 = new XSSFWorkbook(); // or new HSSFWorkbook();
		Sheet sheet2 = wb2.createSheet("rtr");
		// 1. 填充标题行.
		createTitleRow(sheet2);

		// 2. 填充 Employee 信息
		createEmployeeRows(sheet2,list);

		// 3. 设置样式: 包括行高, 列宽, 边框.
		setCellStyle(wb2);
		
		FileOutputStream fileOut = new FileOutputStream("d://rtr2.xlsx");
		wb2.write(fileOut);
		fileOut.close();
		
		System.out.println(list.size());
		System.out.println("恭喜，成功了！");
	}
	
	
	
	//xml文件解析
	public static Map<String,Object> xmlElements(String xmlDoc) throws Exception{
		HashMap<String,Object> map=new HashMap<String,Object>();
	     Map<String,String> map2=new HashMap<String,String>();
         List<Sku> skus=new ArrayList<Sku>();
        //创建一个新的字符串
        StringReader read = new StringReader(xmlDoc);
        //创建新的输入源SAX 解析器将使用 InputSource 对象来确定如何读取 XML 输入
        InputSource source = new InputSource(read);
        //创建一个新的SAXBuilder
        SAXBuilder sb = new SAXBuilder();
        try {
            //通过输入源构造一个Document
            Document doc = sb.build(source);
            //取的根元素
            Element root = doc.getRootElement();
            //System.out.println(root.getName());//输出根元素的名称（测试）
            //得到根元素所有子元素的集合
            List jiedian = root.getChildren();
            //获得XML中的命名空间（XML中未定义可不写）
            //Namespace ns = root.getNamespace();
            Element et = null;
           /* for(int i=0;i<jiedian.size();i++){
                et = (Element) jiedian.get(i);//循环依次得到子元素
               
                System.out.println(et.getChild("users_id").getText());
                System.out.println(et.getChild("users_address").getText());
            }*/
           
            et = (Element) jiedian.get(0);
            List zjiedian = et.getChildren();
            for(int j=0;j<zjiedian.size();j++){
                Element xet = (Element) zjiedian.get(j);
                map.put(xet.getName(), xet.getText());
            }
            
            et = (Element) jiedian.get(1);
            List rrrs=et.getChildren();
            for(int j=0;j<rrrs.size();j++){
            	Element xet = (Element) rrrs.get(j);
            	List rris=xet.getChildren();
            	 Element rrh=(Element)rris.get(0);
            	 List rrhs=rrh.getChildren();
                 for(int k=0;k<rrhs.size();k++){
                     Element rr = (Element) rrhs.get(k);
                     map.put(rr.getName(),rr.getText());
                 }
                 
                 Element rrsl=(Element)rris.get(1);
                 List rrsls=rrsl.getChildren();
                 for(int m=0;m<rrsls.size();m++){
                	 Element rrsles=(Element)rrsls.get(m);
                	 List ns=rrsles.getChildren();
                	 for(int n=0;n<ns.size();n++){
                		 Element e=(Element)ns.get(n);
                		 map2.put(e.getName(), e.getText());
                	 }
                	 Sku sku=(Sku) convertMap(Sku.class,map2);
                	 skus.add(sku);
                 }
                 map.put("skus", skus);
            }
            
        } catch (JDOMException e) {
            // TODO 自动生成 catch 块
            e.printStackTrace();
        } catch (IOException e) {
            // TODO 自动生成 catch 块
            e.printStackTrace();
        }
        return map;
    }
	
	
	
	
	//单元格格式解析
	private static DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
	private static String getCellValue(Cell cell) {
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			return cell.getRichStringCellValue().getString();
		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				Date date = cell.getDateCellValue();
				return dateFormat.format(date)+"";
			} else {
				return cell.getNumericCellValue() + "";
			}
		case Cell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue() + "";
		case Cell.CELL_TYPE_FORMULA:
			return cell.getCellFormula() + "";
		}
		return null;
	}
	
	
	 public static Object convertMap(Class type, Map map)
	            throws IntrospectionException, IllegalAccessException,
	            InstantiationException, InvocationTargetException {
	        BeanInfo beanInfo = Introspector.getBeanInfo(type); // 获取类属性
	        Object obj = type.newInstance(); // 创建 JavaBean 对象

	        // 给 JavaBean 对象的属性赋值
	        PropertyDescriptor[] propertyDescriptors =  beanInfo.getPropertyDescriptors();
	        for (int i = 0; i< propertyDescriptors.length; i++) {
	            PropertyDescriptor descriptor = propertyDescriptors[i];
	            String propertyName = descriptor.getName();

	            if (map.containsKey(propertyName)) {
	                // 下面一句可以 try 起来，这样当一个属性赋值失败的时候就不会影响其他属性赋值。
	                Object value = map.get(propertyName);

	                Object[] args = new Object[1];
	                args[0] = value;

	                descriptor.getWriteMethod().invoke(obj, args);
	            }
	        }
	        return obj;
	    }
    
    
  //设置行的样式
  	private static final String[] TITLES=new String[]{"uniqueKey", "barcode", "transportBarcode",
  			"internalReturnOrder", "returnType", "status", "name", "phone","address","warehouseComment","skuId",
  			"quantity","hostState","held","reasonCode"}; 
  	private static void createTitleRow(Sheet sheet) {
  		Row row = sheet.createRow(0);
  		for(int i=0;i<TITLES.length;i++){
  			row.createCell(i).setCellValue(TITLES[i]);
  		}
  	}
    
  //单元格样式的设置
  	private static CellStyle getCellStyle(Workbook wb) {
  		CellStyle style = wb.createCellStyle();
  		style.setBorderBottom(CellStyle.BORDER_THIN);
  		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());

  		style.setBorderLeft(CellStyle.BORDER_THIN);
  		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());

  		style.setBorderRight(CellStyle.BORDER_THIN);
  		style.setRightBorderColor(IndexedColors.BLACK.getIndex());

  		style.setBorderTop(CellStyle.BORDER_THIN);
  		style.setTopBorderColor(IndexedColors.BLACK.getIndex());

  		return style;
  	}
  	
  //设置单元格的间距
  	private static void setCellStyle(Workbook wb) {
  		Sheet sheet = wb.getSheet("rtr");

  		for (int i = 0; i <= sheet.getLastRowNum(); i++) {
  			Row row = sheet.getRow(i);
  			row.setHeightInPoints(15);

  			for (int j = 0; j < TITLES.length; j++) {
  				Cell cell = row.getCell(j);
  				cell.setCellStyle(getCellStyle(wb));
  			}
  		}

  		for (int j = 0; j < TITLES.length; j++) {
  			sheet.autoSizeColumn(j);
  			sheet.setColumnWidth(j, (int) (sheet.getColumnWidth(j) * 1.3));
  		}
  	}
    
  	
  //为每个单元格填充数据
  	private static void createEmployeeRows(Sheet sheet,List<RtrBean> list) {
  		
  		for (int i = 0; i < list.size(); i++) {
  			RtrBean emp = list.get(i);
  			Row row = sheet.createRow(i + 1);
  			
  			Cell cell = row.createCell(0);
  			cell.setCellValue(emp.getUniqueKey());

  			cell = row.createCell(1);
  			cell.setCellValue(emp.getBarcode());

  			cell = row.createCell(2);
  			cell.setCellValue(emp.getTransportBarcode());

  			cell = row.createCell(3);
  			cell.setCellValue(emp.getInternalReturnOrder());

  			cell = row.createCell(4);
  			cell.setCellValue(emp.getReturnType());

  			cell = row.createCell(5);
  			cell.setCellValue(emp.getStatus());

  			cell = row.createCell(6);
  			cell.setCellValue(emp.getName());

  			cell = row.createCell(7);
  			cell.setCellValue(emp.getPhone());
  			
  			cell = row.createCell(8);
  			cell.setCellValue(emp.getAddress());
  			
  			cell = row.createCell(9);
  			cell.setCellValue(emp.getWarehouseComment());
  			
  			cell = row.createCell(10);
  			cell.setCellValue(emp.getSkuId());
  			
  			cell = row.createCell(11);
  			cell.setCellValue(emp.getQuantity());
  			
  			cell = row.createCell(12);
  			cell.setCellValue(emp.getHostState());
  			
  			cell = row.createCell(13);
  			cell.setCellValue(emp.getHeld());
  			
  			cell = row.createCell(14);
  			cell.setCellValue(emp.getReasonCode());
  			
  			cell=row.createCell(15);
  			cell.setCellValue(";"+emp.getUniqueKey()+";"+emp.getBarcode()+";"+emp.getTransportBarcode()+";"+emp.getInternalReturnOrder()+";"+emp.getReturnType()+";"+emp.getStatus()+";"+emp.getName()+";"+emp.getPhone()+";"+emp.getAddress()+";"+emp.getWarehouseComment()+";"
  					+emp.getSkuId()+";"+emp.getQuantity()+";"+emp.getHostState()+";"+emp.getHeld()+";"+emp.getReasonCode());
  			
  		}
  	}
}
